Amazon Redshift: COPY処理時の『列名指定』について

Amazon Redshiftでは、COPY処理実施の際、テーブルの列名指定を行う事が可能です。当エントリでは小ネタとしてその点について少し深掘った感じで触れて見たいと思います。

• COPY - Amazon Redshift
• デフォルトの列値をロードする - Amazon Redshift

COPY処理実行時、テーブル列名を指定しない場合

まずは検証用テーブル。それぞれ要素が揃った任意のテーブルを1つ、用意してみました。

CREATE TABLE public.spec_table (
  id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  sales BIGINT NOT NULL,
  valid_flg CHAR(1) NOT NULL,
  update_date DATE,
  update_user CHAR(20)
);

通常であればこれに対応するファイルは以下の様なものとなり、

ID,NAME,SALES,VALID_FLG,UPDATE_DATE,UPDATE_USER
1,AAAAA,10000,1,2014/10/28 00:00:00,tesruser_aaa
2,BBBBB,20000,0,2014/10/28 00:00:00,tesruser_bbb
3,CCCCC,30000,1,2014/10/28 00:00:00,tesruser_ccc
4,DDDDD,40000,0,2014/10/28 00:00:00,tesruser_ddd
5,EEEEE,50000,1,2014/10/28 00:00:00,tesruser_eee

こんな感じのCOPY文で投入が完了します。

COPY public.spec_table FROM 's3://<BUCKET_NAME>/<FOLDER_NAME>/normal.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
CSV
DATEFORMAT 'YYYY/MM/DD HH24:MI:SS'
IGNOREHEADER  AS 1;

COPY処理実行時、テーブル列名を指定する場合(テーブル列数＞ファイルの項目列数)

冒頭で言及した様に、実はCOPY文、以下の様にテーブル列名を指定する事が出来ます。

COPY public.spec_table 
(ID, NAME, SALES, VALID_FLG, UPDATE_DATE, UPDATE_USER)
FROM 's3://<BUCKET_NAME>/<FOLDER_NAME>/normal.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
CSV
DATEFORMAT 'YYYY/MM/DD HH24:MI:SS'
IGNOREHEADER  AS 1;

上記SQL文の場合ですとファイルの項目名とテーブル名の列が完全に一致してますのであんまり旨味は無いですが、これまで運用していたクラスタのテーブルに対して仕様変更が発生、仮にファイルがこういう形(テーブル名の項目数＞ファイルの項目列数、という状況)で提供されるようになり、ファイル側の変更がオンプレミス側で厳しい状況にある、となった場合、

ID,NAME,SALES,VALID_FLG
101,AAAAA,10000,1
102,BBBBB,20000,0
103,CCCCC,30000,1
104,DDDDD,40000,0
105,EEEEE,50000,1

こういうふうに項目を指定する事で、テーブルに投入する事が出来ます。ファイルに関する修正・ETLを施す事無く、COPY文実行時のタイミングでその変更を吸収出来るわけです。

COPY public.spec_table FROM 's3://<BUCKET_NAME>/<FOLDER_NAME>/normal.csv'
(ID, NAME, SALES, VALID_FLG)
FROM 's3://tableau-labo-virginia/not-enough.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
CSV
DATEFORMAT 'YYYY/MM/DD HH24:MI:SS'
IGNOREHEADER  AS 1;

指定の無かった項目についてはNULL値が設定されます。

# SELECT * FROM public.spec_table ORDER BY id;
 id  | name  | sales | valid_flg | update_date | update_user 
-----+-------+-------+-----------+-------------+-------------
 101 | AAAAA | 10000 | 1         |             | 
 102 | BBBBB | 20000 | 0         |             | 
 103 | CCCCC | 30000 | 1         |             | 
 104 | DDDDD | 40000 | 0         |             | 
 105 | EEEEE | 50000 | 1         |             | 
(5 rows)

ちなみにCREATE TABLE文で以下の様にNOT NULL制約が設定されている項目に対して上記の様な項目指定無しで投入を試みると、

CREATE TABLE public.spec_table (
  id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  sales BIGINT NOT NULL,
  valid_flg CHAR(1) NOT NULL,
  update_date DATE NOT NULL,
  update_user CHAR(20) NOT NULL
);

以下の様に怒られてしまいました。デフォルト値を指定しなさいと指摘されているので、

ERROR:  NOT NULL column without DEFAULT must be included in column list

以下の様にデフォルト値をテーブル作成時に設定しておくと、4項目指定(ID, NAME, SALES, VALID_FLG)のCOPY文も通ります。

CREATE TABLE public.spec_table (
  id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  sales BIGINT NOT NULL,
  valid_flg CHAR(1) NOT NULL,
  update_date DATE NOT NULL DEFAULT '1999/12/31',
  update_user CHAR(20) NOT NULL DEFAULT 'default-user'
);

結果は以下の様な形に。

# SELECT * FROM public.spec_table ORDER BY id;
 id  | name  | sales | valid_flg | update_date |     update_user      
-----+-------+-------+-----------+-------------+----------------------
 101 | AAAAA | 10000 | 1         | 1999-12-31  | default-user        
 102 | BBBBB | 20000 | 0         | 1999-12-31  | default-user        
 103 | CCCCC | 30000 | 1         | 1999-12-31  | default-user        
 104 | DDDDD | 40000 | 0         | 1999-12-31  | default-user        
 105 | EEEEE | 50000 | 1         | 1999-12-31  | default-user        
(5 rows)

COPY処理実行時、テーブル列名を指定する場合(テーブル列数＜ファイルの項目列数)

では上記とは逆のケースだとどうなるでしょうか。以下6項目で構成されているテーブルに対し、

CREATE TABLE public.spec_table (
  id INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  sales BIGINT NOT NULL,
  valid_flg CHAR(1) NOT NULL,
  update_date DATE NOT NULL DEFAULT '1999/12/31 23:59:59',
  update_user CHAR(20) NOT NULL DEFAULT 'default-user'
);

項目列数が余計に存在していた場合、どういう挙動を示すか？というようなケースです。

ID,NAME,SALES,VALID_FLG,UPDATE_DATE,UPDATE_USER,EXTRA1,EXTRA2
20101,AAAAA,10000,1,2014/10/28 00:00:00,tesruser_aaa,xxx,yyy
20102,BBBBB,20000,0,2014/10/28 00:00:00,tesruser_bbb,xxx,yyy
20103,CCCCC,30000,1,2014/10/28 00:00:00,tesruser_ccc,xxx,yyy
20104,DDDDD,40000,0,2014/10/28 00:00:00,tesruser_ddd,xxx,yyy
20105,EEEEE,50000,1,2014/10/28 00:00:00,tesruser_eee,xxx,yyy

こちらについてはエラーとなってしまいました。

COPY public.spec_table FROM 's3://<BUCKET_NAME>/<FOLDER_NAME>/normal.csv'
(ID, NAME, SALES, VALID_FLG)
FROM 's3://tableau-labo-virginia/not-enough.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
CSV
DATEFORMAT 'YYYY/MM/DD HH24:MI:SS'
IGNOREHEADER  AS 1;
ERROR:  Load into table 'spec_table' failed.  Check 'stl_load_errors' system table for details.

エラーを見るとそのまんま、項目が多いよ！と怒られてしまいました。この辺りを吸収するオプション指定も無さそうなので、テーブル名に対してファイルの項目名が多いような場合はやはりファイル生成の時点で項目を合わせる、若しくは別テーブルに一旦ロードしておいて、そのテーブルから必要な項目を抜き出した形で投入(INSERT 〜 SELECT)するしか無さそうですね。

# SELECT
#   TRIM(line_number) AS line_number,
#   TRIM(raw_line) AS raw_line,
#   TRIM(err_code) AS err_code,
#   TRIM(err_reason) AS err_reason
# FROM
#   stl_load_errors ORDER BY starttime DESC LIMIT 1;
 line_number |                           raw_line                           | err_code |      err_reason       
-------------+--------------------------------------------------------------+----------+-----------------------
 2           | 20101,AAAAA,10000,1,2014/10/28 00:00:00,tesruser_aaa,xxx,yyy | 1202     | Extra column(s) found
(1 row)

まとめ

以上、COPY文実行時に於ける列名指定オプションに関する解説でした。通常であれば投入ファイルの列名とテーブルの列名は一致していると思われますので今回の様なオプション指定もそこまで出番が無いのかなとは思いますが、こういった利用方法もあるというのを頭の片隅に置いといて頂けると幸いです。こちらからは以上です。